# coding:utf-8

import tornado.web
import tornado.ioloop
import tornado.options
import tornado.httpserver
import json
import torndb

from tornado.options import options
from tornado.web import RequestHandler

tornado.options.define("port", default=8000, type=int, help="run server on the given port")


class BaseHandler(RequestHandler):
    """请求处理基类"""
    def prepare(self):
        if self.request.headers.get("Content-Type", "").startswith("application/json"):
            try:
                self.json_args = json.loads(self.request.body)
            except Exception as e:
                self.json_args = {}


class IndexHandler(BaseHandler):
    """请求处理类"""
    def get(self):
        sql = 'insert into houses(title,position,price,score, comments)\
                values("国际软件园大厦1", "中关 村软件园", 60000, 5, 3)'
        try:
            house_id = self.application.db.execute(sql)
        except Exception as e:
            print(e)
            self.write("DB error")
        else:
            self.write("house id: %s" % house_id)

    def post(self):
        title = self.get_argument("title")
        position = self.get_argument("position")
        price = self.get_argument("price")
        score = self.get_argument("score")
        comments = self.get_argument("comments")

        # 字符串拼接
        # sql = 'insert into houses(title,position,price,score, comments)\
        #        values("%s", "%s", %s, %s, %s)' % (title, position, price, score, comments)

        """
        select count(*) from user

        where user_name="1" or 1=1 or ("1"="1" and user_password="a") or "1"="1"

        name= 1" or 1=1 or ("1"="1
        password = a") or "1"="1


        if count(*)>0:
            登录成功

        where user_name=%s
        user_name=`1" or 1=1 or ("1"="1`
        """

        # 动态参数
        # sql = 'insert into houses(title,position,price,score, comments)\
        #         values(%s, %s, %s, %s, %s)'
        sql = 'insert into houses(title,position,price,score, comments)\
                values(%(title)s, %(position)s, %(price)s, %(score)s, %(comments)s)'

        try:
            # house_id = self.application.db.execute(sql, title, position, price, score, comments)
            house_id = self.application.db.execute(sql,  position=position,title=title,
                                                   price=price, score=score, comments=comments)
        except Exception as e:
            print(e)
            self.write("DB error")
        else:
            self.write("house id: %s" % house_id)


class UpdateHandler(BaseHandler):
    """更新sql"""
    def post(self):
        title = self.get_argument("title")
        position = self.get_argument("position")
        price = self.get_argument("price")
        score = self.get_argument("score")
        comments = self.get_argument("comments")
        sql = 'update houses set title=%(title)s, price=%(price)s where id=8'
        try:
            ret = self.application.db.execute_rowcount(sql, title=title, price=price)
        except Exception as e:
            print(e)
            self.write("DB error")
        else:
            self.write("row count: %s" % ret)


class GetHandler(BaseHandler):
    """获取单条结果"""
    def get(self):
        house_id = self.get_argument("hid")
        sql = 'select title,position,price  from houses where id=%s;'
        try:
            ret = self.application.db.get(sql, house_id)
        except Exception as e:
            print(e)
            self.write("DB error")
        else:
            if ret:
                data = dict(
                    t=ret["title"],
                    p=ret["position"],
                    pr=ret["price"],
                )
                self.write(data)
            else:
                self.write(dict(errmsg="no data"))


class QueryHandler(BaseHandler):
    """获取多条结果"""
    def get(self):
        num = self.get_argument("num")
        sql = 'select title,position,price  from houses limit %s;'
        try:
            ret = self.application.db.query(sql, int(num))
        except Exception as e:
            print(e)
            self.write("DB error")
        else:
            if ret:
                li = []
                for data in ret:
                    house = dict(
                        title=data["title"],
                        position=data["position"],
                        price=data["price"],
                    )
                    li.append(house)
                self.write(dict(data=li))
            else:
                self.write(dict(errmsg="no data"))

class Application(tornado.web.Application):
    """"""
    def __init__(self, *args, **kwargs):
        super(Application, self).__init__(*args, **kwargs)
        self.db = torndb.Connection(
            host="127.0.0.1",
            database="itcast",
            user="root",
            password="mysql"
        )


if __name__ == "__main__":
    tornado.options.parse_command_line()
    settings = {
        "debug": True,
    }
    # app = tornado.web.Application(
    #     [(r"/", IndexHandler)],
    #     **settings
    # )
    app = Application(
        [
            (r"/", IndexHandler),
            (r"/update", UpdateHandler),
            (r"/get", GetHandler),
            (r"/query", QueryHandler),
        ],
        **settings
    )
    http_server = tornado.httpserver.HTTPServer(app)
    http_server.listen(options.port)
    tornado.ioloop.IOLoop.current().start()